import pandas as pd
import statsmodels.formula.api as smf
from sqlalchemy import create_engine
import pymysql

db_config = {
    "host":'localhost',
    'user':'root',
    'password':'sjk1234',
    'database':'tushare',
    'port':3306,
    'charset':'utf8mb4'
}
engine = create_engine(f"mysql+pymysql://{db_config['user']}:{db_config['password']}@{db_config['host']}:{db_config['port']}/{db_config['database']}?charset={db_config['charset']}")

conn = pymysql.connect(**db_config)
chunk_size = 10000

df = pd.read_sql_query("SELECT * FROM date_1 WHERE date_1.trade_date BETWEEN '2023-01-01' AND '2023-12-31' and date_1.ts_code = '000001.SZ'",
                       conn, 
                       chunksize=chunk_size
                       ) 
                       

df1 =pd.concat(df,ignore_index=True)
df1['zd_closes'] = round((df1['closes'] - df1['closes'].shift(1)) /df1['closes'].shift(1),2)

df1=df1.dropna(subset=['zd_closes'])
print(df1)
ex = ['id','trade_date','ts_code','the_data','opens','high','low','closes','pre_closes','change','pct_change','vol','amount']
number=df1.select_dtypes(include=['number']).columns.to_list()
number_list = [i for i in number if i not in ex]

formula = 'zd_closes ~ ' + ' + '.join(number_list)
res = smf.ols(formula, data=df1).fit()
print(res.summary())
